﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Data;

namespace TableEmanee.Dal
{
    class MembersEntity
    {
      public Int32 ID {get;set;}
      public string Name {get;set;}
      public string Supervisor {get;set;}
      public string Magmoaa { get; set; }
      public DateTime Join_Date { get; set;}

    }
    class Members
    {
        private static String connstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='evaluation.accdb' ;Jet OLEDB:Database Password=Yarabe11";
        private static String cmdText = "";


        public static bool insert(MembersEntity m)
        {
            bool flag = false;
            cmdText = "INSERT INTO Members (Name,Supervisor,Magmoaa,Join_Date) values (?,?,?,?)";
            OleDbConnection con = new OleDbConnection(connstring);
            OleDbCommand cmd = new OleDbCommand(cmdText, con);

            cmd.Parameters.AddWithValue("@Name", m.Name);
            cmd.Parameters.AddWithValue("@Supervisor", m.Supervisor);
            cmd.Parameters.AddWithValue("@Magmoaa", m.Magmoaa);
            cmd.Parameters.AddWithValue("@Join_Date", m.Join_Date);
         

            try
            {
                con.Open();
                Int32 x = cmd.ExecuteNonQuery();
                if (x > 0)
                {
                    flag = true;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("Error In Insert" + e.Message);
            }
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return flag;

        }
        public static bool Update(MembersEntity m)
        {
            bool flag = false;
            cmdText = "Update Members set Name= @Name , Supervisor = @Supervisor ,Magmoaa=@Magmoaa, Join_Date =@Join_Date where ID = @ID";
            OleDbConnection con = new OleDbConnection(connstring);
            OleDbCommand cmd = new OleDbCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Name", m.Name);
            cmd.Parameters.AddWithValue("@Supervisor", m.Supervisor);
            cmd.Parameters.AddWithValue("@Magmoaa", m.Magmoaa);
            cmd.Parameters.AddWithValue("@Join_Date", m.Join_Date);
            cmd.Parameters.AddWithValue("@ID", m.ID);

            try
            {
                con.Open();
                Int32 x = cmd.ExecuteNonQuery();
                if (x > 0)
                {
                    flag = true;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("Error In Update" + e.Message);
            }
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return flag;


        }
        public static bool delete(int id)
        {
            bool flag = true;
            cmdText = "delete from Members where ID = ? ";
            OleDbConnection con = new OleDbConnection(connstring);
            OleDbCommand cmd = new OleDbCommand(cmdText, con);

            cmd.Parameters.AddWithValue("@ID", id);

            try
            {
                con.Open();
                int x = cmd.ExecuteNonQuery();
                if (x == 0)
                {
                    flag = false;

                }
            }
            catch (Exception e)
            {
                MessageBox.Show("Error in Delet" + e.Message);

            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }

            }
            return flag;

        }
        public static MembersEntity GetById(int x)
        {
            MembersEntity t = null;
            cmdText = "Select * from Members where ID=?";

            OleDbConnection con = new OleDbConnection(connstring);
            OleDbCommand cmd = new OleDbCommand(cmdText, con);

            cmd.Parameters.AddWithValue("@ID", x);
            try
            {
                con.Open();
                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    t = fillEntityFromReader(reader);
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("GetMemberId ", ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return t;
        } 
        public static List<MembersEntity> GetAll()
        {
            List<MembersEntity> listEntity = new List<MembersEntity>();
            MembersEntity t = null;
            cmdText = "Select * from Members";

            OleDbConnection con = new OleDbConnection(connstring);
            OleDbCommand cmd = new OleDbCommand(cmdText, con);

            try
            {
                con.Open();
                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    t = fillEntityFromReader(reader);
                    listEntity.Add(t);

                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("GetMemberId ", ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return listEntity;

        }
        public static List<MembersEntity> GetByName(String Name)
        {
            List<MembersEntity> listEntity = new List<MembersEntity>();
            MembersEntity t = null;
            cmdText = "Select * from Members where Name Like ? ";

            OleDbConnection con = new OleDbConnection(connstring);
            OleDbCommand cmd = new OleDbCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Name", Name);

            try
            {
                con.Open();
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    t = fillEntityFromReader(reader);
                    listEntity.Add(t);

                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("GetByNamebox ", ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return listEntity;




        }
        public static MembersEntity fillEntityFromReader(OleDbDataReader reader)
        {
            MembersEntity t = new MembersEntity();
            if (reader["ID"] != null)
                t.ID = Int32.Parse(reader["ID"].ToString());
            if (reader["Name"] != null)
                t.Name = reader["Name"].ToString();
            if (reader["Supervisor"] != null)
                t.Supervisor = reader["Supervisor"].ToString();
            if (reader["Magmoaa"] != null)
                t.Magmoaa = reader["Magmoaa"].ToString();
            if (reader["Join_Date"] != null)
                t.Join_Date =DateTime.Parse( reader["Join_Date"].ToString());
           
            return t;

        }
    }
}
